package com.example.exam.repository;

import com.example.exam.entity.ExamPaperAnswer;
import com.example.exam.entity.QuestionAnswer;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;
import java.util.List;

/**
 * @author shishuqian
 * date 2021/4/27
 * time 15:16
 **/
@Repository
public interface QuestionAnswerRepository extends JpaRepository<QuestionAnswer,Integer> {

    //根据问题id查询所有用户填写的答案
    @Query(value = "select user_answer from t_question_answer where question_id = ?",nativeQuery = true)
    List<String> queryAllAnswerByQuestionId(Integer question_id);

    //统计某选项被选次数
    @Query(value = "select count(user_answer) from t_question_answer where question_id = ?1 and user_answer = ?2",nativeQuery = true)
    int countChoice(Integer question_id,String user_answer);

    //根据试卷id，统计填写此试卷的人数
    @Query(value = "select count(distinct user_id) from t_question_answer where exam_paper_id = ?",nativeQuery = true)
    int countFilledPeople(Integer exam_paper_id);

    @Query(value = "SELECT p FROM ExamPaperAnswer p WHERE p.examPaperId = ?1 ORDER BY p.finalScore DESC ,p.systemScore DESC ")
    List<ExamPaperAnswer> viewRanking(Integer examPaperId);

    //@Query(value = "select user_answer from t_question_answer where question_id = ?",nativeQuery = true)
    @Query(value = "SELECT p FROM QuestionAnswer p WHERE p.questionId = ?1")
    List<QuestionAnswer> queryAllAnswer(Integer questionId);

    //查询多选某选项的所有人
    @Query(value = "select user_id from t_question_answer where question_id = ?1 and (user_answer = ?2 or user_answer like ?3 escape '$' or user_answer like ?4 escape '$' or user_answer like ?5 escape '$')",nativeQuery = true)
    List<Integer> findNameDuo(Integer questionId,String a,String b,String c,String d);

    //查询选单选,判断的人
    @Query(value = "select p.userId from QuestionAnswer p where p.questionId = ?1 and p.userAnswer = ?2")
    List<Integer> findNameDan(Integer questionId,String userAnswer);

}
